{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/raw_sourceB/mall上海.csv', encoding='CP936',skiprows=1,\n",
    "            names=['lng', 'lat', 'r'] + ['f' + str(i) for i in range(17)] + ['useless', 'hotness']\n",
    "           )\n",
    "\n",
    "df.drop('useless', axis=1, inplace=True)\n",
    "\n",
    "dates = list(filter(lambda x: x.startswith('.') is False,\n",
    "       os.listdir('data/raw_sourceB/201501Shanghai/'),\n",
    "  ))\n",
    "\n",
    "traffic_o = pd.DataFrame()\n",
    "traffic_d = pd.DataFrame()\n",
    "\n",
    "for d in dates:\n",
    "    temp_o = pd.read_csv('data/raw_sourceB/201501Shanghai/{}/Mall上海_O.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_o = temp_o.sum(axis=1)\n",
    "    assert temp_o.shape[0] == df.shape[0]\n",
    "    traffic_o[d] = temp_o\n",
    "    temp_d = pd.read_csv('data/raw_sourceB/201501Shanghai/{}/Mall上海_D.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_d = temp_d.sum(axis=1)\n",
    "    assert temp_d.shape[0] == df.shape[0]\n",
    "    traffic_d[d] = temp_d\n",
    "\n",
    "df['f17'] = traffic_o.sum(axis=1)\n",
    "df['f18'] = traffic_d.sum(axis=1)\n",
    "df = df[['lng', 'lat', 'r'] + ['f' + str(i) for i in range(19)] + ['hotness']]\n",
    "df.to_csv('data/sourceB/shanghai_mall.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 61,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/raw_sourceB/Street上海.csv', encoding='CP936',skiprows=1,\n",
    "            names=['lng', 'lat', 'r'] + ['f' + str(i) for i in range(17)] + ['useless1','useless2','hotness']\n",
    "           )\n",
    "\n",
    "df.drop(['useless1', 'useless2'], axis=1, inplace=True)\n",
    "\n",
    "dates = list(filter(lambda x: x.startswith('.') is False,\n",
    "       os.listdir('data/raw_sourceB/201501Shanghai/'),\n",
    "  ))\n",
    "\n",
    "traffic_o = pd.DataFrame()\n",
    "traffic_d = pd.DataFrame()\n",
    "\n",
    "for d in dates:\n",
    "    temp_o = pd.read_csv('data/raw_sourceB/201501Shanghai/{}/BusinessStreet上海_O.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_o = temp_o.sum(axis=1)\n",
    "    assert temp_o.shape[0] == df.shape[0]\n",
    "    traffic_o[d] = temp_o\n",
    "    temp_d = pd.read_csv('data/raw_sourceB/201501Shanghai/{}/BusinessStreet上海_D.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_d = temp_d.sum(axis=1)\n",
    "    assert temp_d.shape[0] == df.shape[0]\n",
    "    traffic_d[d] = temp_d\n",
    "\n",
    "df['f17'] = traffic_o.sum(axis=1)\n",
    "df['f18'] = traffic_d.sum(axis=1)\n",
    "df = df[['lng', 'lat', 'r'] + ['f' + str(i) for i in range(19)] + ['hotness']]\n",
    "df.to_csv('data/sourceB/shanghai_street.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/raw_sourceB/mall北京.csv', encoding='CP936',skiprows=1,\n",
    "            names=['lng', 'lat', 'r'] + ['f' + str(i) for i in range(17)] + ['useless', 'hotness']\n",
    "           )\n",
    "\n",
    "df.drop('useless', axis=1, inplace=True)\n",
    "\n",
    "dates = list(filter(lambda x: x.startswith('.') is False,\n",
    "       os.listdir('data/raw_sourceB/201406BeiJing/'),\n",
    "  ))\n",
    "\n",
    "traffic_o = pd.DataFrame()\n",
    "traffic_d = pd.DataFrame()\n",
    "\n",
    "for d in dates:\n",
    "    temp_o = pd.read_csv('data/raw_sourceB/201406BeiJing/{}/Mall北京_O.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_o = temp_o.sum(axis=1)\n",
    "    assert temp_o.shape[0] == df.shape[0]\n",
    "    traffic_o[d] = temp_o\n",
    "    temp_d = pd.read_csv('data/raw_sourceB/201406BeiJing/{}/Mall北京_D.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_d = temp_d.sum(axis=1)\n",
    "    assert temp_d.shape[0] == df.shape[0]\n",
    "    traffic_d[d] = temp_d\n",
    "\n",
    "df['f17'] = traffic_o.sum(axis=1)\n",
    "df['f18'] = traffic_d.sum(axis=1)\n",
    "df = df[['lng', 'lat', 'r'] + ['f' + str(i) for i in range(19)] + ['hotness']]\n",
    "df.to_csv('data/sourceB/beijing_mall.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 74,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df = pd.read_csv('data/raw_sourceB/street北京.csv', encoding='CP936',skiprows=1,\n",
    "            names=['lng', 'lat', 'r'] + ['f' + str(i) for i in range(17)] + ['useless','hotness']\n",
    "           )\n",
    "\n",
    "df.drop(['useless'], axis=1, inplace=True)\n",
    "\n",
    "dates = list(filter(lambda x: x.startswith('.') is False,\n",
    "       os.listdir('data/raw_sourceB/201406BeiJing/'),\n",
    "  ))\n",
    "\n",
    "traffic_o = pd.DataFrame()\n",
    "traffic_d = pd.DataFrame()\n",
    "\n",
    "for d in dates:\n",
    "    temp_o = pd.read_csv('data/raw_sourceB/201406BeiJing/{}/BusinessStreet北京_O.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_o = temp_o.sum(axis=1)\n",
    "    assert temp_o.shape[0] == df.shape[0]\n",
    "    traffic_o[d] = temp_o\n",
    "    temp_d = pd.read_csv('data/raw_sourceB/201406BeiJing/{}/BusinessStreet北京_D.txt'.format(d), sep='\\t', header=-1,\n",
    "               ).drop([0, 1, 2, 27], axis=1)\n",
    "    temp_d = temp_d.sum(axis=1)\n",
    "    assert temp_d.shape[0] == df.shape[0]\n",
    "    traffic_d[d] = temp_d\n",
    "\n",
    "df['f17'] = traffic_o.sum(axis=1)\n",
    "df['f18'] = traffic_d.sum(axis=1)\n",
    "df = df[['lng', 'lat', 'r'] + ['f' + str(i) for i in range(19)] + ['hotness']]\n",
    "df.to_csv('data/sourceB/beijing_street.csv')\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
